/*
 * Copyright (c) 2008-2021 浩瀚深度 All Rights Reserved.
 *
 * <p>FileName: DataController.java</p>
 *
 * @author zz
 * @date 2024/3/1
 * @version 1.0
 * History:
 * v1.0.0 2024/3/1 Create
 */
package com.sqlweb.sqlweb.controller;

import com.sqlweb.sqlweb.base.Result;
import com.sqlweb.sqlweb.base.ResultCode;
import com.sqlweb.sqlweb.entity.ExecuteSql;
import com.sqlweb.sqlweb.service.SqlService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * <p>Title: DataController</p>
 * <p>Description: </p>
 *
 * @author zz
 */
@Slf4j
@RestController
@RequestMapping("/data")
@CrossOrigin(origins = "*")
public class DataController {
    @Autowired
    SqlService sqlService;

    @PostMapping("/queryData")
    public Result queryData() {
        ExecuteSql executeSql = new ExecuteSql();
        executeSql.setDataSourceId(1);
        executeSql.setSqlStatement("SELECT\n" +
                "    tp.id AS 探针ID,\n" +
                "    tp.name AS 设备名称,\n" +
                "    tp.ipaddr AS 设备IP,\n" +
                "    te.name AS 机房名称,\n" +
                "    ty.name AS 设备类型,\n" +
                "    tb.physisolt AS 槽位号,\n" +
                "    tb2.name AS 单板类型,\n" +
                "    SUM(tl.bandwidth) AS 带宽,\n" +
                "    tpi.mibver AS MIB版本,\n" +
                "    tpi.commver AS 通信协议, \n" +
                "    MAX(CASE WHEN tpv.moduleid = 0 THEN tpv.version END) AS 整机TCR版本,\n" +
                "    MAX(CASE WHEN tpv.moduleid = 1 THEN tpv.version END) AS 主控程序,\n" +
                "    MAX(CASE WHEN tpv.moduleid = 4 THEN tpv.version END) AS 主板主文件,\n" +
                "    -- MAX(CASE WHEN tpv.moduleid = 5 THEN tpv.version END) AS 主板镜像文件,\n" +
                "    MAX(CASE WHEN tpv.moduleid = 10 THEN tpv.version END) AS 业务定义文件,\n" +
                "    MAX(CASE WHEN tpv.moduleid = 11 THEN tpv.version END) AS 特征定义文件,\n" +
                "    SUM(CASE WHEN d.statindex = 0 THEN d.upbps ELSE 0 END) AS 出境速率,\n" +
                "    SUM(CASE WHEN d.statindex = 0 THEN d.dnbps ELSE 0 END) AS 入境速率,\n" +
                "    SUM(CASE WHEN d.statindex = 1 THEN d.upbps ELSE 0 END) AS 出境FIFO速率,\n" +
                "    SUM(CASE WHEN d.statindex = 1 THEN d.dnbps ELSE 0 END) AS 入境FIFO速率,\n" +
                "    MAX(tps.snmpstate) AS SNMP状态,\n" +
                "    MAX(tps.tcpstate) AS TCP状态\n" +
                "FROM\n" +
                "    tm_board tb\n" +
                "LEFT JOIN\n" +
                "    tm_probe tp ON tp.id = tb.probeid\n" +
                "LEFT JOIN\n" +
                "    tm_eqptroom te ON te.id = tp.roomid\n" +
                "LEFT JOIN\n" +
                "    tr_probetype ty ON ty.id = tp.type\n" +
                "LEFT JOIN\n" +
                "    tr_boardtype tb2 ON tb2.id = tb.type\n" +
                "LEFT JOIN\n" +
                "    tm_link tl ON tl.boardid = tb.id\n" +
                "LEFT JOIN\n" +
                "    tm_probe_version tpv ON tpv.probeid = tp.id\n" +
                "LEFT JOIN\n" +
                "    tm_probe_info tpi ON tpi.probeid = tp.id\n" +
                "LEFT JOIN\n" +
                "    td_probe_5min_d_20240301 d ON tb.id = tp.id\n" +
                "LEFT JOIN (\n" +
                "    SELECT * FROM td_probe_state_5min_d_20240301\n" +
                "    WHERE time = (SELECT MAX(time) FROM td_probe_state_5min_d_20240301)\n" +
                ") tps ON tps.probeid  = tp.id\n" +
                "GROUP BY\n" +
                "    tb.id, tp.id, tp.name, tp.ipaddr, te.name, ty.name, tb.physisolt, tb2.name, tpi.mibver, tpi.commver;\n");
        Object resultData = sqlService.executeSql(executeSql);
        // 返回统一的结果
        return new Result(ResultCode.SUCCESS, resultData);
    }
}
